import * as fs from 'fs'
import * as path from 'path'

import { DbImportProvider } from '../dialects/core/dbImportProvider'
import { ProviderFactory } from '../dialects/core/providerFactory'
import { MarkdownReader } from '../lib/markdown-object/markdownReader'
import { MarkdownWriter } from '../lib/markdown-object/markdownWriter'
import { FileHelper } from '../lib/utils/fileHelper'
import { DatabaseTypes } from '../models/databaseTypes'
import { DbModel } from '../models/dbModel'
import { DbTable } from '../models/dbTable'
import { DbmOptions } from './dbmOptions'
import { DbOptions } from './dbOptions'

/**
 * The database modeling markdown service.
 */
export class DbmService {
	/**
	 * New a markdown file (.db.md) that defines a table.
	 * The file will be located at `options.sourceFolder` with name `<tableName>.db.md`.
	 * @param options the options (required: `sourceFolder`)
	 * @param tableName The table name
	 */
	public static newDbmMarkdownFile(options: DbmOptions, tableName: string): string {
		const model: DbModel = {
			tables: [
				{
					name: tableName,
					comment: 'The new table comment',
					primaryKey: {
						columns: 'TableId, TableId2',
					},
					indexes: [
						{
							columns: '',
							unique: true,
						},
					],
					foreignKeys: [
						{
							columns: '',
							refTable: '',
							refColumns: '',
							options: '',
						},
					],
					columns: [
						{
							name: 'TableId',
							label: 'Table Id',
							type: 'int',
							nullable: false,
							default: '',
							comment: 'The Id column',
						},
						{
							name: 'TableName',
							label: 'Table Name',
							type: 'nvarchar(50)',
							nullable: false,
							default: "''",
							comment: 'The name column',
						},
					],
				},
			],
		}
		const table: DbTable = model.tables[0]
		return MarkdownWriter.renderTable(
			table,
			options.sourceFolder &&
				path.join(options.sourceFolder, `${table.name}${options.mdFileExtension}`),
		)
	}

	/**
	 * Generate a SQL file with create-table-if-not-exists scripts.
	 * @param options the options, required: `database`, `sourceFolder`, `targetPath`.
	 */
	public static toCreateTableIfNotExistsSql(options: DbmOptions): string {
		const model: DbModel = this.toModel(options)

		const provider = ProviderFactory.getProvider(options.database)
		const sql = provider.getCreateTableIfNotExistsSql(model, true)

		if (options.targetPath) {
			FileHelper.writeFileSync(options.targetPath, sql)
		}
		return sql
	}

	/**
	 * Generate a SQL file with create-table scripts.
	 * @param options the options, required: `database`, `sourceFolder`, `targetPath`.
	 */
	public static toCreateTableSql(options: DbmOptions): string {
		const model: DbModel = this.toModel(options)

		const provider = ProviderFactory.getProvider(options.database)
		const sql = provider.getCreateTableSql(model, true)

		if (options.targetPath) {
			FileHelper.writeFileSync(options.targetPath, sql)
		}
		return sql
	}

	/**
	 * Generate a SQL file with drop-table scripts.
	 * @param options the options, required: `database`, `sourceFolder`, `targetPath`.
	 */
	public static toDropTableSql(options: DbmOptions): string {
		const model: DbModel = this.toModel(options)

		const provider = ProviderFactory.getProvider(options.database)
		const sql = provider.getDropTableSql(model, true)

		if (options.targetPath) {
			FileHelper.writeFileSync(options.targetPath, sql)
		}
		return sql
	}

	/**
	 * Generate a SQL file with drop-then-create-table scripts.
	 * @param options the options, required: `database`, `sourceFolder`, `targetPath`.
	 */
	public static toDropThenCreateTableSql(options: DbmOptions): string {
		const model: DbModel = this.toModel(options)

		const provider = ProviderFactory.getProvider(options.database)
		const sql = provider.getDropThenCreateTableSql(model, true)

		if (options.targetPath) {
			FileHelper.writeFileSync(options.targetPath, sql)
		}
		return sql
	}

	/**
	 * Generate a SQL file with upgrade scripts.
	 * support:
	 * - new tables,
	 * - new columns,
	 * - new primary keys
	 * - new indexes
	 * - new foreign keys
	 * @param options the options, required: `database`, `sourceFolder`, `targetPath`, `previousVersionFolder`.
	 */
	public static toUpgradeSql(options: DbmOptions): string {
		const fromOptions: DbmOptions = new DbmOptions()
		fromOptions.database = options.database
		fromOptions.mdFileExtension = options.mdFileExtension
		fromOptions.sourceFolder = options.previousVersionFolder
		const fromModel: DbModel = this.toModel(fromOptions)
		const model: DbModel = this.toModel(options)

		const provider = ProviderFactory.getProvider(options.database)
		const sql = provider.getUpgradeSql(fromModel, model, true)

		if (options.targetPath) {
			FileHelper.writeFileSync(options.targetPath, sql)
		}
		return sql
	}

	/**
	 * Generate a JSON file.
	 * @param options the options, required: `database`, `sourceFolder`, `targetPath`.
	 */
	public static toJson(options: DbmOptions): string {
		const model: DbModel = this.toModel(options)
		if (options.targetPath) {
			FileHelper.writeJsonFileSync(options.targetPath, model)
		}
		return JSON.stringify(model, null, 2)
	}

	/**
	 * Generate markdown files from a json file
	 * @param jsonFile the json file path
	 * @param markdownFolder the folder to which we will write
	 * @param force If true, delete all files whose extension matched, default: `false`.
	 * @param fileExtension generated file extension, default: `.db.md`.
	 */
	public static fromJsonFile(
		jsonFile: string,
		markdownFolder: string,
		force: boolean = false,
		fileExtension: string = '.db.md',
	) {
		const model: DbModel = JSON.parse(FileHelper.readJsonFileSync(jsonFile))
		this.toMarkdown(model, markdownFolder, force, fileExtension)
	}

	/**
	 * Generate markdown files from a model
	 * @param model the model object with
	 * @param markdownFolder the folder to which we will write
	 * @param force If true, delete all files whose extension matched, default: `false`.
	 * @param fileExtension generated file extension, default: `.db.md`.
	 */
	public static toMarkdown(
		model: DbModel,
		markdownFolder: string,
		force: boolean = false,
		fileExtension: string = '.db.md',
	) {
		if (force && fs.existsSync(markdownFolder)) {
			FileHelper.removeAllFilesSync(markdownFolder, fileExtension)
		}
		if (!fs.existsSync(markdownFolder)) {
			FileHelper.mkdirSync(markdownFolder)
		}
		model.tables.forEach((table: DbTable) => {
			MarkdownWriter.renderTable(table, path.join(markdownFolder, `${table.name}${fileExtension}`))
		})
	}

	/**
	 * Generate a index file for a model
	 * @param model the model object with
	 * @param markdownFolder the folder to which we will write
	 * @param filename generated file name, default: `index.md`.
	 */
	public static genIndexFile(
		model: DbModel,
		markdownFolder: string,
		filename: string = 'index.md',
	) {
		const file = path.join(markdownFolder, filename)
		FileHelper.removeFileSync(file)

		let data = '# Database Design\n\n'
		data += '## Index\n\n'

		const columnWidths = [5, 6]
		model.tables.forEach((table: DbTable) => {
			if (table.name.length * 2 + 10 > columnWidths[0]) {
				columnWidths[0] = table.name.length * 2 + 10
			}
			if (table.comment && table.comment.length > columnWidths[1]) {
				columnWidths[1] = table.comment.length
			}
		})

		data += `| ${'Table'.padEnd(columnWidths[0], ' ')} | ${'Comment'.padEnd(
			columnWidths[1],
			' ',
		)} |\n`
		data += `| ${''.padEnd(columnWidths[0], '-')} | ${''.padEnd(columnWidths[1], '-')} |\n`
		model.tables.forEach((table: DbTable) => {
			const name = `[${table.name}](${table.name}.db.md)`
			data += `| ${name.padEnd(columnWidths[0], ' ')} | ${table.comment?.padEnd(
				columnWidths[1],
				' ',
			)} |\n`
		})

		FileHelper.writeFileSync(file, data)
	}

	/**
	 * Get a model object.
	 * @param options the options, required: `database`, `sourceFolder`.
	 */
	public static toModel(options: DbmOptions): DbModel {
		const model: DbModel = { tables: [] }
		const files: fs.Dirent[] = FileHelper.readdirSync(options.sourceFolder)

		files.forEach((file) => {
			if (file.name.endsWith(options.mdFileExtension)) {
				const table = new MarkdownReader(`${options.sourceFolder}/${file.name}`).toObject()

				model.tables.push(table)
			}
		})

		return model
	}

	/**
	 * Generate markdown files from a database
	 * @param dbOptions the datbase connection configure
	 * @param markdownFolder the folder to which we will write
	 * @param force If true, delete all files whose extension matched, default: `false`.
	 * @param fileExtension generated file extension, default: `.db.md`.
	 */
	public static async fromDatabase(
		dbOptions: DbOptions,
		markdownFolder: string,
		force: boolean = false,
		fileExtension: string = '.db.md',
	) {
		const provider: DbImportProvider = ProviderFactory.getImportProvider(DatabaseTypes.mariadb)
		const model = await provider.getModel(dbOptions)
		this.toMarkdown(model, markdownFolder, force, fileExtension)
		// generate an index.md for the imported model
		this.genIndexFile(model, markdownFolder)
	}
}
